package com.zhiyou.dao;

import com.zhiyou.model.House;
import com.zhiyou.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class HouseDao {
    public List<House> getHouse(int pageNo,int pageSize,HashMap<String,String> keywordMap) {
        ArrayList<House> houses = new ArrayList<>();
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        //将关键字数据取出
        String keyword = keywordMap.get("keyword");
        String hdirS = keywordMap.get("hdirS");
        String lowpriceStr = keywordMap.get("lowprice");
        double lowprice = Double.parseDouble(lowpriceStr==null || lowpriceStr==""?"0":lowpriceStr);
        String highpriceStr = keywordMap.get("highprice");
        double highprice = Double.parseDouble(highpriceStr==null|| highpriceStr==""?"0":highpriceStr);
        String sql="select * from house where haddress like concat(\"%\",?,\"%\")"+
                " and hdir like concat(\"%\",?,\"%\")"+
                " and hprice between ? and ?"+" limit ?,?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,keyword==null?"":keyword);
            ps.setString(2,hdirS==null?"":hdirS);
            ps.setDouble(3,lowprice);
            ps.setDouble(4,highprice==0?Integer.MAX_VALUE:highprice);
            ps.setInt(5,(pageNo-1)*pageSize);
            ps.setInt(6,pageSize);
            System.out.println("日志:查全部sql"+ps.toString());
            rs = ps.executeQuery();
            while(rs.next()){
                House house=new House();
                house.setHid(rs.getInt("hid"));
                house.setHaddress(rs.getString("haddress"));
                house.setHfloor(rs.getString("hfloor"));
                house.setHroomNum(rs.getInt("hroomNum"));
                house.setHarea(rs.getString("harea"));
                house.setHdir(rs.getString("hdir"));
                house.setHdeco(rs.getInt("hdeco"));
                house.setHair(rs.getInt("hair"));
                house.setHprice(rs.getDouble("hprice"));
                house.setHrentStatus(rs.getInt("hrentStatus"));
                house.setHimage(rs.getString("himage"));
                house.setHaddTime(rs.getDate("haddTime"));
                house.setHupdateTime(rs.getDate("hupdateTime"));
                System.out.println(house.getHid());
                houses.add(house);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return houses;
    }

    public void deleteById(int hid) {
        Connection conn = DBUtil.getConn();
        String sql="delete from house where hid=?";
        PreparedStatement ps=null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,hid);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps);
        }
    }

    public House selectById(int hid) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        House house=null;
        String sql="select * from house where hid=?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,hid);
            rs=ps.executeQuery();
            while (rs.next()){
                house=new House();
                house.setHid(rs.getInt("hid"));
                house.setHaddress(rs.getString("haddress"));
                house.setHfloor(rs.getString("hfloor"));
                house.setHroomNum(rs.getInt("hroomNum"));
                house.setHarea(rs.getString("harea"));
                house.setHdir(rs.getString("hdir"));
                house.setHdeco(rs.getInt("hdeco"));
                house.setHair(rs.getInt("hair"));
                house.setHprice(rs.getDouble("hprice"));
                house.setHrentStatus(rs.getInt("hrentStatus"));
                house.setHimage(rs.getString("himage"));
                house.setHaddTime(rs.getDate("haddTime"));
                house.setHupdateTime(rs.getDate("hupdateTime"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return house;
    }

    public void updateNew(int hid, String haddress, String hfloor, int hroomNum, String harea, String hdir, int hdeco, int hair, double hprice, int hrentStatus, String himage, String haddTime, String hupdateTime) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        String sql="update house set haddress=?,hfloor=?,hroomNum=?,harea=?,hdir=?,hdeco=?,hair=?,hprice=?,hrentStatus=?,himage=?,haddTime=?,hupdateTime=? where hid=?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,haddress);
            ps.setString(2,hfloor);
            ps.setInt(3,hroomNum);
            ps.setString(4,harea);
            ps.setString(5,hdir);
            ps.setInt(6,hdeco);
            ps.setInt(7,hair);
            ps.setDouble(8,hprice);
            ps.setInt(9,hrentStatus);
            ps.setString(10,himage);
            ps.setString(11,haddTime);
            ps.setString(12,hupdateTime);
            ps.setInt(13,hid);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps);
        }
    }

    public void addNew(String haddress, String hfloor, int hroomNum, String harea, String hdir, int hdeco, int hair, double hprice, int hrentStatus, String himage, String haddTime, String hupdateTime) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        String sql="insert into house(haddress,hfloor,hroomNum,harea,hdir,hdeco,hair,hprice,hrentStatus,himage,haddTime,hupdateTime) values(?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,haddress);
            ps.setString(2,hfloor);
            ps.setInt(3,hroomNum);
            ps.setString(4,harea);
            ps.setString(5,hdir);
            ps.setInt(6,hdeco);
            ps.setInt(7,hair);
            ps.setDouble(8,hprice);
            ps.setInt(9,hrentStatus);
            ps.setString(10,himage);
            ps.setString(11,haddTime);
            ps.setString(12,hupdateTime);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps);
        }
    }

    public int getHouseNum(HashMap<String,String> keywordMap) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        int total=0;
        //将关键字数据取出
        String keyword = keywordMap.get("keyword");
        String hdirS = keywordMap.get("hdirS");
        String lowpriceStr = keywordMap.get("lowprice");
        double lowprice = Double.parseDouble(lowpriceStr==null || lowpriceStr==""?"0":lowpriceStr);
        String highpriceStr = keywordMap.get("highprice");
        double highprice = Double.parseDouble(highpriceStr==null|| highpriceStr==""?"0":highpriceStr);
        String sql="select count(hid) total from house where haddress like concat(\"%\",?,\"%\")"
                +" and hdir like concat(\"%\",?,\"%\")"
                +" and hprice between ? and ?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,keyword==null?"":keyword);
            ps.setString(2,hdirS==null?"":hdirS);
            ps.setDouble(3,lowprice);
            ps.setDouble(4,highprice==0?Integer.MAX_VALUE:highprice);
            System.out.println("日志:查个数"+ps.toString());
            rs=ps.executeQuery();
            while (rs.next()){
                total = rs.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return total;
    }
}
